﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Data.SQLite;
using System.Data.SqlClient;

namespace SEO.Spider
{
    /// <summary>
    /// 版权所有: 版权所有(C) 2011，上海云里网络科技有限公司
    /// 内容摘要: 添加采集到的电子邮件 通用函数
    /// 完成日期：2011年10月22日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class ESEmails
    {
        public static int iNum = 0;

        /// <summary>
        /// 添加采集到的电子邮件
        /// </summary>
        /// <param name="strEmail">电子邮件</param>
        /// <param name="strKeywordName">关键词</param>
        /// <param name="strWebUrl">来源网页</param>
        public static void InsertEmails(string strEmail,string strKeywordName,string strWebUrl,bool bAPISoft,string strWebName)
        {
            string strMessage = "";
            try
            {
                SEO.DAL.ESEmailsDAL emaildal = new DAL.ESEmailsDAL();

                //新增过滤电子邮件规则
                //2012-08-06
                strEmail = strEmail.ToLower();
                string[] splitEmail = strEmail.Split(new char[] {'@'});
                string strAtLeft = splitEmail[0];
                string strAtRight = splitEmail[1];
                if (strAtLeft.Length < 2)
                {
                    //@左侧小于2位数不加入数据库
                }
                else if (strAtRight.Length < 5)
                {
                    //@右则小于5位数不加入数据库
                }
                else if (strAtRight.Length >= 16)
                {
                    //@右则大于16位数不加入数据库
                }
                else
                {
                    //@右则如果包含“--”
                    if (strAtRight.Contains("--"))
                    {
                        strAtRight = strAtRight.Replace("--","");
                    }

                    //@右则如果包含“_”
                    //取右则最后一个字母
                    string strAtRightLastWord = strAtRight.Substring(strAtRight.Length - 1, 1);
                    char cAtRightLastWord = Convert.ToChar(strAtRightLastWord);
                    if (!char.IsLetter(cAtRightLastWord))//判断是否为字母
                    {
                        strAtRight = strAtRight.Replace(strAtRightLastWord, "");
                    }

                    string strNewEmail = strAtLeft + "@" + strAtRight;

                    //@右则常用邮箱多余字母过滤
                    string[] domainArray = { "126.com", "163.com", "yeah.net", "qq.com","sina.com","sina.cn","foxmail.com","sohu.com","tom.com","netease.com","21cn.com","gmail.com", "hotmail.com", "live.cn", "yahoo.com", "msn.cn", "yahoo.com.cn", "yahoo.cn", "sogou.com", "189.cn", "139.com", "188.com", "wo.com.cn", "263.com"};
                    for (int k = 0; k < domainArray.Length; k++)
                    {
                        string strDomain = domainArray[k];
                        if (strNewEmail.Contains(strDomain))
                        {
                            string strGetDomainEmail = strNewEmail.Replace(strDomain, "#");
                            string[] splitDomainEmail = strGetDomainEmail.Split(new char[]{'#'});
                            strNewEmail = splitDomainEmail[0] + strDomain;
                        }
                    }

                    //2013-2-19发现采集有.comqq情况
                    if (strNewEmail.Contains(".comqq"))
                    {
                        strNewEmail = strNewEmail.Replace(".comqq", ".com");
                    }
                    
                    int iEmailID = emaildal.GetAutoAddNum();
                    if (!emaildal.CheckEmailAddressIsExist(strNewEmail))
                    {
                        SEO.Model.ESEmails email = new Model.ESEmails();
                        email.EmailID = iEmailID;
                        email.EmailAddress = strNewEmail;
                        email.Keywords = strKeywordName;
                        email.EmailSource = strWebUrl;
                        email.WebName = strWebName;
                        SEO.BLL.ESEmailsBLL emailbll = new BLL.ESEmailsBLL();
                        emailbll.InsertESEmailsBLL(email);


                        //添加到服务器
                        //InsertServerEMails(strNewEmail);

                        //如果勾选了API软件接口，则自动加入云里邮件营销软件中
                        if (bAPISoft)
                        {
                            int iUserID = int.Parse(SEO.Utility.PublicValue.GetPublicValue());
                            if (iNum % 3000 == 0 && iNum == 0)
                            {
                                //自动生成分组
                                APIInsertGroup("cloudin-email-group-" + DateTime.Now.ToString("yyyyMMddHHmm"), iUserID);
                            }
                            else
                            {
                                APIInsertEMails(strNewEmail, iUserID, APIGetMaxGroupID());
                            }

                            iNum++;
                            strMessage = strNewEmail + "," + iUserID + "," + APIGetMaxGroupID() + ",";
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("添加采集过来的电子邮件：" + ex.Message + "," + strMessage);
            }
        }

        public static void APIInsertEMails(string strEmail, int iUserID, int iGroupID)
        {
            try
            {
                string strConn = SEO.Utility.PublicValue.GetNewsValue();
                SQLiteConnection cn = new SQLiteConnection(strConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "insert into Emails(mail_id,mail_email,user_id,group_id) " +
                    " values(" + APIEmailsGetAutoAddNum() + ",'" + strEmail + "'," + iUserID + "," + iGroupID + ")";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("插入电子邮件 -> " + ex.Message);
            }
        }

        /// <summary>
        /// Emails获取最大ID
        /// </summary>
        /// <returns></returns>
        public static int APIEmailsGetAutoAddNum()
        {
            int iNum = 0;
            try
            {
                string strConn = SEO.Utility.PublicValue.GetNewsValue();
                SQLiteConnection cn = new SQLiteConnection(strConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select mail_id from Emails order by mail_id desc limit 1";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["mail_id"].ToString()) + 1;
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("Emails获取最大ID -> " + ex.Message);
            }

            return iNum;
        }


        /// <summary>
        /// 插入分组名称
        /// </summary>
        /// <param name="strGroupName">分组名称</param>
        /// <param name="iUserID">用户ID</param>
        public static void APIInsertGroup(string strGroupName, int iUserID)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.Utility.PublicValue.GetNewsValue());
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "insert into EmailGroup(group_id,group_name,user_id,add_date) " +
                    " values(" + APIGroupGetAutoAddNum() + ",'" + strGroupName + "'," + iUserID + ",datetime('now','localtime'))";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("插入分组名称 -> " + ex.Message);
            }
        }

        /// <summary>
        /// Group获取最大ID并自增1
        /// </summary>
        /// <returns></returns>
        public static int APIGroupGetAutoAddNum()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.Utility.PublicValue.GetNewsValue());
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select group_id from EmailGroup order by group_id desc limit 1";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["group_id"].ToString()) + 1;
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("Group获取最大ID -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 获取Group分组中最大ID
        /// </summary>
        /// <returns></returns>
        public static int APIGetMaxGroupID()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.Utility.PublicValue.GetNewsValue());
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select group_id from EmailGroup order by group_id desc limit 1";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["group_id"].ToString());
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("Group获取最大ID -> " + ex.Message);
            }

            return iNum;
        }

        public static void InsertServerEMails(string strEmail)
        {
            try
            {
                string strConn = "server=222.73.68.28;uid=cloudin;pwd=cloudin!@#53344521;database=CloudinEmails;pooling=true";
                SqlConnection cn = new SqlConnection(strConn);
                cn.Open();
                string strSql = "insert into Emails(email_address) values('" + strEmail + "')";
                SqlCommand cm = new SqlCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("添加电子邮件到远程服务器 -> " + ex.Message);
            }
        }
    }
}
